/**
 * Created by jackliu on 15/12/16.
 */

var common = require('../../common');
var pool = require('../index');
var mysql = require('mysql');
var ItbBaseModel = require('./ItbBaseModel');


// Schema 结构
var ItbPartnerModel = function(obj){
    ItbBaseModel.call(this,obj);

  // 以下为表定义数据，需要的字段就填入
  //  `partner_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '合伙人id',
  //`apply_id` bigint(20) NOT NULL COMMENT '申请id（关联申请表）',
  //`user_id` bigint(20) NOT NULL COMMENT '用户id',
  //`level` int(3) NOT NULL DEFAULT '10' COMMENT '合伙人级别：10普通、20高级',
  //`type` int(3) DEFAULT NULL COMMENT '合伙人类型: 预留',
  //`name` varchar(100) DEFAULT NULL COMMENT '姓名',
  //`mobile` varchar(100) DEFAULT NULL COMMENT '电话',
  //`bank` varchar(100) DEFAULT NULL COMMENT '开户行',
  //`bank_account` varchar(100) DEFAULT NULL COMMENT '银行帐号',
  //`desc` text COMMENT '备注',
  //`create_time` datetime NOT NULL,
  //`update_time` datetime NOT NULL,
  //`update_by` varchar(50) NOT NULL DEFAULT '',
  //`del_flg` int(1) NOT NULL DEFAULT '0' COMMENT '删除flg',


};
ItbPartnerModel.prototype = new ItbBaseModel();

// 取得表名
ItbPartnerModel.prototype.getTableName = function(){
    return 'itb_partner';
}
// 取得查询语句中where 后面匹配主键的条件（需要各派生类进行重载）
ItbPartnerModel.prototype.getWhereConditionOfKeyEqual = function(){
    return 'id='+this.id;
}



//------------------------------------------------------------------
/* 检测手机用户是否能成为为合伙人的下线
 * 参数 partnerUID: 合伙人用户id
 * 参数 mobile: 抽奖人手机号
 * 返回 callback， 参数如下：
 *          error：
 *          ret：1表示验证通过
 * 验证规则：1、下线必须是注册用户（即mobile对应的有user id）
 *          2、合伙人之间不能成为下线
 *          3、一个下线只能属于一个合伙人，不能属于多个合伙人
 *          4、如果用户A领取了多个合伙人的优惠券，则按A成为最早的优惠券的合伙人的下线
 * */
ItbPartnerModel.prototype.checkPartnerCustomCondition = function(partnerUID, mobile, callback){
    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql =
            ' (select 1 as `key`, u.`user_id` as `value` from itb_user u where u.mobile='+mobile+' and u.del_flg=0) '+
            ' union '+
            ' (select 2 as `key`, p.`user_id` as `value` from `itb_partner` p,itb_user u where p.`user_id`=u.`user_id` and u.mobile='+mobile+'  and p.del_flg=0  and u.del_flg=0) '+
            ' union '+
            ' (select 3 as `key`, p.`user_id` as `value`  from `itb_partner_cust` pc,itb_user u,`itb_partner` p where pc.`user_id`=u.`user_id` and u.mobile='+mobile+' and u.`del_flg`=0 and p.`del_flg`=0 and pc.`partner_id`=p.`partner_id` and pc.del_flg=0) '+
            ' union '+
            ' (select 4 as `key`,c.`channel_u` as `value` from `itb_coupon` c where c.`mobile`='+mobile+'  and c.del_flg=0 order by c.`draw_time` desc limit 0,1) ';

        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            var ret = false;
            if(result && result.length>0) {
                /*
                    key=1,value返回注册用的user id，不存在则为空
                    key=2,value返回mobile对应的合伙人的user id，不存在则为空
                    key=3,value返回mobile对应的上线合伙人的user id，不存在则为空
                    key=4,value返回mobile对应的优惠劵的渠道用户的user id，不存在则为空
                */
                function getValueByKey(key, result){
                    for(var i=0;i<result.length;i++){
                        var one = result[i];
                        if(one && one.key==key){
                            return one.value;
                        }
                    }
                    return null;
                }
                var v1 = getValueByKey(1, result);
                var v2 = getValueByKey(2, result);
                var v3 = getValueByKey(3, result);
                var v4 = getValueByKey(4, result);

                ret = (v1)
                    &&(!v2)
                    &&(!v3)
                    &&(v4==partnerUID );
            }
            callback(error, ret);
        });
    });
};


/**
 * @param retFlg: 0(按合伙人分组返回),1(0结果集的总count数),2(取总体合计金额)
 * @param userId: 高级合伙人id
 * @param areaInfo: {"prov":150000,"city":150500,"dist":150522}
 * @param stTime: "2017-01-02 12:12:12"
 * @param edTime: "2017-02-02 12:12:12"
 */
function sql_GetHighPartnerExIncome(retFlg, userId, areaInfo, stTime, edTime){

    var sql =
        ' SELECT xx.prov, xx.city, xx.dist '+
        ' ,xx.prov_name, xx.city_name, xx.dist_name '+
        ' ,xx.partner_id, xx.mobile '+
        ' ,xx.self '+
        ' ,SUM(xx.total_fee)-SUM(xx.order_mail_fee) AS sum_total_fee '+
        ' ,SUM(xx.order_income) AS sum_income ';
    if(retFlg==1) {
        sql = ' SELECT count(*) AS cnt FROM ( SELECT xx.dist, xx.partner_id ';
    } else if(retFlg==2) {
        sql = ' SELECT SUM(xx.total_fee)-SUM(xx.order_mail_fee) AS sum_total_fee '+// 去邮费
            ' ,SUM(xx.order_income) AS sum_income ';
    }

    sql += ' FROM '+
        ' ( SELECT o.order_id, pa.dist, p2.partner_id, pa.prov, pa.city '+
        '         ,a1.name AS prov_name, a2.name AS city_name, a3.name AS dist_name '+
        '         ,p.partner_id AS partner_id_myself, u.mobile '+
        '         ,CASE WHEN p2.partner_id=p.partner_id THEN 1 ELSE 0 END AS self '+
        '         ,o.total_fee, SUM(od.mail_fee) AS order_mail_fee '+
        '         ,func_calc_partner_income(o.order_id, 20) AS order_income '+
        ' FROM itb_partner p '+// 高级合伙人自己
        '     ,itb_partner_area pa '+// 高级合伙人管辖区域
        '     ,itb_partner p2 '+// 目标合伙人
        /* add by JackLiu 20170721:  筛选出高级合伙人的辖区内的普通合伙人*/
        '     ,itb_partner_application p2apl '+// 普通合伙人的地址信息
        /* add end */
        '     ,itb_user u '+
        '     ,itb_partner_cust pc2 '+// 目标合伙人下线
        '     ,itb_trade_order o '+
        '     ,itb_trade_order_detail od '+
        //'     ,itb_address a '+
        '     ,itb_area a1 '+
        '     ,itb_area a2 '+
        '     ,itb_area a3 '+
        ' WHERE p.del_flg=0 AND p.level=20 AND p.user_id= '+userId+
        ' AND pa.del_flg=0 AND pa.partner_id=p.partner_id '+
        ' AND a1.del_flg=0 AND a1.id=pa.prov '+
        ' AND a2.del_flg=0 AND a2.id=pa.city '+
        ' AND a3.del_flg=0 AND a3.id=pa.dist ';
    if(areaInfo && areaInfo.prov) {
        sql += ' AND pa.prov= '+areaInfo.prov;
    }
    if(areaInfo && areaInfo.city) {
        sql += ' AND pa.city= '+areaInfo.city;
    }
    if(areaInfo && areaInfo.dist) {
        sql += ' AND pa.dist= '+areaInfo.dist;// 指定区域
    }
    sql +=
        ' AND p2.del_flg=0 '+
        ' AND u.del_flg=0 AND u.user_id=p2.user_id '+
        ' AND pc2.del_flg=0 AND pc2.partner_id=p2.partner_id '+
        ' AND o.del_flg=0 AND o.state=40 AND o.type=0 '+// 完成的普通订单
        ' AND o.user_id=pc2.user_id '+
        ' AND o.create_time>=pc2.start_time AND o.create_time<=pc2.end_time '+
        /* remove by JackLiu 20170721:高级合伙人的分成与订单地址无关
        ' AND a.del_flg=0 AND a.addr_id=o.addr_id '+
        ' AND a.dist=pa.dist '+// 管辖区域内的订单
         */
        ' AND od.del_flg=0 AND od.order_id=o.order_id ';

    /* add by JackLiu 20170721:  筛选出高级合伙人的辖区内的普通合伙人*/
       sql +=
        ' AND p2apl.del_flg=0 '+
        ' AND p2apl.id=p2.apply_id '+
        ' AND p2apl.dist= pa.dist ';// p2apl.dist是普通合伙人所在区域，pa.dist是高级合伙人的辖区
    /* add end */

    if(stTime && stTime.length>0) {
        sql += ' AND o.finish_time>= "'+stTime+'"';
    }
    if(edTime && edTime.length>0) {
        sql += ' AND o.finish_time<= "'+edTime+'"';
    }
    sql += ' GROUP BY o.order_id ) xx';// 先按订单分组

    if(retFlg==2) {
        sql += ' GROUP BY xx.partner_id_myself ';
    } else if(retFlg==1) {
        sql += ' GROUP BY xx.dist, xx.partner_id ) tt ';
    } else {
        sql += ' GROUP BY xx.dist, xx.partner_id '+
            ' ORDER BY xx.dist, xx.partner_id DESC ';
    }

    return sql;
}

/**
 * 获取高级合伙人所选区域的所选时间段内该区域对应的订单的合伙人抽成列表
 * @param userId: 高级合伙人id
 * @param areaInfo: {"prov":150000,"city":150500,"dist":150522}
 * @param stTime: "2017-01-02 12:12:12"
 * @param edTime: "2017-02-02 12:12:12"
 * @param pageNum
 * @param pageSize
 * @param callback
 */
ItbPartnerModel.prototype.getHighPartnerExIncome = function(userId, areaInfo, stTime, edTime, pageNum, pageSize, callback){
    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql = sql_GetHighPartnerExIncome(0, userId, areaInfo, stTime, edTime);

        pageNum = Number(pageNum||'0') || 0;
        pageSize = Number(pageSize||'0') || 12;
        sql += ' LIMIT '+pageNum*pageSize+', '+pageSize;

        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            callback(error, result);
        });
    });
};
ItbPartnerModel.prototype.getHighPartnerExIncomeCnt = function(userId, areaInfo, stTime, edTime, callback){
    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql = sql_GetHighPartnerExIncome(1, userId, areaInfo, stTime, edTime);

        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            if(result && result.length>0){
                callback(error, result[0].cnt);
            }else{
                callback(error, 0);
            }
        });
    });
};
ItbPartnerModel.prototype.getHighPartnerExIncomeTotal = function(userId, areaInfo, stTime, edTime, callback){
    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql = sql_GetHighPartnerExIncome(2, userId, areaInfo, stTime, edTime);

        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            if(result && result.length>0){
                callback(error, result[0]);
            }else{
                callback(error, null);
            }
        });
    });
};


/**
 * @param bForCnt
 * @param userId: 高级合伙人id
 * @param partnerId: 指定的合伙人id
 * @param areaInfo: {"prov":150000,"city":150500,"dist":150522}
 * @param stTime: "2017-01-02 12:12:12"
 * @param edTime: "2017-02-02 12:12:12"
 */
function sql_GetHighPartnerExIncomeDetail(bForCnt, userId, partnerId, areaInfo, stTime, edTime){

    var sql =
        ' SELECT o.order_id, u.mobile '+
        ' ,o.total_fee-SUM(od.mail_fee) AS total_fee '+
        ' ,func_calc_partner_income(o.order_id, 20)  AS income '+
        ' ,DATE_FORMAT(o.create_time, "%Y-%m-%d") as create_time '+
        ' ,DATE_FORMAT(o.finish_time, "%Y-%m-%d") as finish_time ';
    if(bForCnt) {
        sql = ' SELECT count(*) AS cnt FROM ( SELECT o.order_id ';
    }
    sql +=
        ' FROM itb_partner p '+// 高级合伙人自己
        '     ,itb_partner_area pa '+// 高级合伙人管辖区域
        '     ,itb_partner p2 '+// 目标合伙人
        '     ,itb_partner_cust pc2 '+// 目标合伙人下线
        '     ,itb_user u '+
        '     ,itb_trade_order o '+
        '     ,itb_trade_order_detail od '+
        //'     ,itb_address a '+
        ' WHERE p.del_flg=0 AND p.level=20 AND p.user_id= '+userId+
        ' AND pa.del_flg=0 AND pa.partner_id=p.partner_id ';
    if(areaInfo && areaInfo.prov) {
        sql += ' AND pa.prov= '+areaInfo.prov;
    }
    if(areaInfo && areaInfo.city) {
        sql += ' AND pa.city= '+areaInfo.city;
    }
    if(areaInfo && areaInfo.dist) {
        sql += ' AND pa.dist= '+areaInfo.dist;// 指定区域
    }
    sql +=
        ' AND p2.del_flg=0 AND p2.partner_id= '+partnerId+// 指定目标合伙人
        ' AND pc2.del_flg=0 AND pc2.partner_id=p2.partner_id '+
        ' AND u.del_flg=0 AND u.user_id=pc2.user_id '+
        ' AND o.del_flg=0 AND o.state=40 AND o.type=0 '+// 完成的普通订单
        ' AND o.user_id=pc2.user_id '+
        ' AND o.create_time>=pc2.start_time AND o.create_time<=pc2.end_time '+
        /* remove by JackLiu 20170721:高级合伙人的分成与订单地址无关
        ' AND a.del_flg=0 AND a.addr_id=o.addr_id '+
        ' AND a.dist=pa.dist '+// 管辖区域内的订单
        */
        ' AND od.del_flg=0 AND od.order_id=o.order_id ';
    if(stTime && stTime.length>0) {
        sql += ' AND o.finish_time>= "'+stTime+'"';
    }
    if(edTime && edTime.length>0) {
        sql += ' AND o.finish_time<= "'+edTime+'"';
    }

    if(bForCnt) {
        sql += ' GROUP BY o.order_id ) x ';
    } else {
        sql += ' GROUP BY o.order_id '+
            ' ORDER BY o.finish_time DESC ';
    }

    return sql;
}

/**
 * 获取城市合伙人某区域内某时间段内成单明细(额外收益)
 * @param userId: 高级合伙人id
 * @param partnerId: 指定的合伙人id
 * @param areaInfo: {"prov":150000,"city":150500,"dist":150522}
 * @param stTime: "2017-01-02 12:12:12"
 * @param edTime: "2017-02-02 12:12:12"
 * @param pageNum
 * @param pageSize
 * @param callback
 */
ItbPartnerModel.prototype.getHighPartnerExIncomeDetail = function(userId, partnerId, areaInfo, stTime, edTime, pageNum, pageSize, callback){
    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql = sql_GetHighPartnerExIncomeDetail(false, userId, partnerId, areaInfo, stTime, edTime);

        pageNum = Number(pageNum||'0') || 0;
        pageSize = Number(pageSize||'0') || 12;
        sql += ' LIMIT '+pageNum*pageSize+', '+pageSize;

        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            callback(error, result);
        });
    });
};
ItbPartnerModel.prototype.getHighPartnerExIncomeDetailCnt = function(userId, partnerId, areaInfo, stTime, edTime, callback){
    pool.pool.getConnection(function(err, connection) {
        // Use the connection
        var sql = sql_GetHighPartnerExIncomeDetail(true, userId, partnerId, areaInfo, stTime, edTime);

        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            if(result && result.length>0){
                callback(error, result[0].cnt);
            }else{
                callback(error, 0);
            }
        });
    });
};


//
/* 获取城市合伙人信息
* @param userId: 高级合伙人id
* @param callback
*/
ItbPartnerModel.prototype.getPartnermessage = function(userId,callback){
    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbPartnerModel.js [getPartnermessage] Err:'+err);
            return callback(err,null);
        }
        var sql =
            ' SELECT p.* ' +
            ' FROM itb_partner p ' +
            ' WHERE p.del_flg=0 ' +
            ' AND p.user_id='+userId
    console.log(sql);
    connection.query(sql, function(error, result) {
        // And done with the connection.
        connection.release();

        if(result && result.length>0){
            callback(error, result);
        }else{
            callback(error, {});
        }
    });
    });
}




//--------------------------------end-------------------------------

// 导出
module.exports = ItbPartnerModel;